<?php
namespace App\Util;
/**
 * sql server 数据库操作类
 * @author Hezone
 * 2018-9-6 10:27:35
*/
use App\Models\TCUser;
use phpDocumentor\Reflection\DocBlock\Tags\Var_;
$SqlServer = null;

class SqlServerUtil{
	public static  $mssql;

	//获取表单条数据
	static function find($server, $table = null, $where = []){
		$where = SqlServerUtil::_arrayToWhere($where);
		$query = sqlsrv_query($server, "select * from ".$table.$where);
		return SqlServerUtil::_toArray($query, 0);
	}

	//获取表单条数据../../images/goods.jpg
	static function get($server, $table = null, $where = [], $select = [], $limit = false){
		if(!$server){
			return false;
		}

		$select = SqlServerUtil::_createSelect($select);
		$where = SqlServerUtil::_arrayToWhere($where);
		if($limit){
			//ORDER BY ActionTime DESC
			$query = sqlsrv_query($server, "select TOP ".$limit." ". $select ." from ".$table.$where);
		}else{
			$query = sqlsrv_query($server, "select ". $select ." from ".$table.$where);
		}

		return SqlServerUtil::_toArray($query);
	}

	//执行sql语句
	static function execSql($server, $sqlStr, $return = 1){
		if($server == false){
			return false;
		}
		$execSql = sqlsrv_query($server, $sqlStr);
		$t = sqlsrv_errors();
		if($server == false || $t){
			dd(iconv('GBK', 'UTF-8', $t[0]['message']));
		}
		if($execSql && $return == 3){
			return true;
		}
		return $return == 1 ? SqlServerUtil::_toArray($execSql) : [];
	}

	//修改数据
	static function update($server, $table = null, $data = [], $where = []){
		$tableName = '';
		$tableValue = '';
		$setStr = "";
		$whereStr = "";
		foreach ($data as $key => $value) {
			$setStr .= $key."='".$value."',";
		}
		$setStr = substr($setStr,0,strlen($setStr)-1);
		if(count($where)){
			foreach ($where as $key => $value) {
				$whereStr .= $key."='".$value."',";
			}
			
			$whereStr = " WHERE ".substr($whereStr,0,strlen($whereStr)-1);
		}
		$update = sqlsrv_query($server, "update {$table} set {$setStr} {$whereStr}");
		return $update;
	}

	//增加数据
	static function create($server, $table = null, $data = null){
		$data = SqlServerUtil::_arrayToSql($data);
		$insert = sqlsrv_query($server, "insert into {$table}({$data['table_name']}) values({$data['table_value']})");
		$t = sqlsrv_errors();
		
		if($server == false || $t){
			dd(iconv('GBK', 'UTF-8', $t[0]['message']));
		}
		if($insert){
			$res = SqlServerUtil::execSql($server,"select IDENT_CURRENT('yd_goods')");
			return ['id' => $res[0][0]];
		}
		return false;
	}

	//循环插入
	static function forCreate($server, $table = null, $data = null){
		$count = count($data);
		$temp = [];
		foreach ($data as $index => $item) {
			$data = SqlServerUtil::_arrayToSql($item);

			$str = "insert into {$table}({$data['table_name']}) values({$data['table_value']})";
			$update = sqlsrv_query($server, $str);
			if($update){
				array_push($temp, $update);
			}else{
// 				dd(iconv('GBK', 'UTF-8', sqlsrv_errors()[0]['message']));  

			}
		}
		return $temp;
		return count($temp) ==$count ? true : false;
	}

	//自动生成编码
    static function getStockNO($server, $type){
    	$time = date('Y-m-d',time());
    	$key = $type.date('Ymd',time())."0000";
    	$sql = '';
    	switch ($type) {
    		case 'DB':
    			$sql = "select count(*) as num  from WInStockMaster where inStockNo like 'DB%' and inStockTime >= convert(datetime,'{$time} 00:00:00.000')";
    			break;
    		case 'SL':
    			$sql = "select count(*) as num  from WInStockMaster where inStockNo like 'SL%' and inStockTime >= convert(datetime,'{$time} 00:00:00.000')";
    			break;
    		case 'WI':
    			$sql = "select count(*) as num  from WInStockMaster where inStockNo like 'WI%' and inStockTime >= convert(datetime,'{$time} 00:00:00.000')";
    			break;
    		case 'CI':
    			$sql = "select count(*) as num  from WInStockMaster where inStockNo like 'CI%' and inStockTime >= convert(datetime,'{$time} 00:00:00.000')";
    			break;
    		case 'SO':
    			$sql = "select count(*) as num  from WOutStockMaster where outStockNo like 'SO%' and outStockTime >= convert(datetime,'{$time} 00:00:00.000')";
    			break;
    		case 'QI':
    			$sql = "select count(*) as num  from WInStockMaster where inStockNo like 'QI%' and inStockTime >= convert(datetime,'{$time} 00:00:00.000')";
    			break;
    		case 'QO':
    			$sql = "select count(*) as num  from WOutStockMaster where outStockNo like 'QO%' and outStockTime >= convert(datetime,'{$time} 00:00:00.000')";
    			break;
    		case 'Out':
    			$sql = "select count(*) as num  from WInStockMaster where inStockNo like 'Out%' and inStockTime >= convert(datetime,'{$time} 00:00:00.000')";
    			break;
    		case 'QC':
    			$sql = "select count(*) as num  from WInStockMaster where inStockNo like 'QC%' and inStockTime >= convert(datetime,'{$time} 00:00:00.000')";
    			break;
    		default:
    			$sql = "select count(*) as num  from WInStockMaster where inStockNo like 'QC%' and inStockTime >= convert(datetime,'{$time} 00:00:00.000')";
    			break;
    	}
    	$query = sqlsrv_query($server, $sql);
    	$row = sqlsrv_fetch_array($query);

    	if(isset($row[0])){

    		$key = substr($key, 0, 7).(substr($key, 7, 5) + ($row['num'] + 1));
    	}
    	return $key;
    }

	//数组转化为 where条件
	static function _arrayToWhere($data){
		if(count($data) <= 0) return '';
		$where = " where ";
		foreach ($data as $index => $item) {
			end($data); 
			if(key($data) == $index){
				$where .= "{$index}='{$item}'";
			}else{
				$where .= "{$index}='{$item}' and ";
			}
		}
		return $where;
	}
	
	static function _arrayToWhereFids($data){
		if(count($data) <= 0) return '';
		$where = " where ";
		foreach ($data as $index => $item) {
			end($data);
			if(key($data) == $index){
				$where .= "{$index}  in  ({$item}) ";
			}
		}
		return $where;
	}
	
	
	//处理sql查下结果转化为数组
	static function _toArray($query, $type = 1){
		if(!$query) return false;
		$result = [];
		while($row = sqlsrv_fetch_array($query)){

			foreach ($row as $key => $value) {
				//sql server 乱码处理
				if(is_string($row[$key])){
					$row[$key] = iconv('GBK', 'UTF-8', $row[$key]);
				}
			}

		  	array_push($result, $row);
		}
		if(count($result) == 0) return [];
		switch ($type) {
			case 0:
				$result = $result[0];
				break;
			default:
				$result = $result;
				break;
		}
		return $result;
	}

	//生成插入或修改数据 10258221385@chatroom  8753159218@chatroom
	static function _arrayToSql($data){

		$result = ['table_name' => '', 'table_value' => ''];
		$name = [];
		$value = [];
		foreach ($data as $index => $item) {
			if($item){
				$item = str_replace("\r","",$item);
				$item = str_replace("\n","",$item);
				if(!is_array($item)){
					array_push($name, $index);
					if(is_numeric($item)){
						array_push($value, $item);
					}else{
						array_push($value, "'".$item."'");
					}
					
				}
			}
		}
		$result['table_name'] = implode(",", $name);
		$result['table_value'] = implode(",", $value);
		return $result;
	}

	//生成 select 
	static function _createSelect($select){

		return count($select) ? implode(",", $select) : "*";
	}

	//根据前端cookie返回数据库
	static function _getDBInfo($db){
		$db = json_decode($db, true);
		return $db;
	}

	//lareavel cookie解密
	static function _cookieEnCode($payload){
		//加密方法       config/app.php配置：cipher        
		$secret_type = 'AES-256-CBC';   
		$key = env("APP_KEY");      
		//处理laravel_session        
		$payload = json_decode(base64_decode($payload), true);     

		$iv = base64_decode($payload['iv']);        

		//处理KEY        
		$key = base64_decode(substr($key, 7));  

		//解密        
		$decrypted = \openssl_decrypt($payload['value'], $secret_type, $key, 0, $iv);       
		//反序列化        
		$decrypted = unserialize($decrypted);       
		return $decrypted;
	}

	//查询master的账号密码配置
	static function _getUserInfo($Fname, $Fsid){
		$SqlServer = sqlsrv_connect(env("DB_HOST"),[ "UID" => env("DB_USERNAME"), "PWD" => env("DB_PASSWORD"), "Database" => env("DB_DATABASE")]);
        $user = SqlServerUtil::execSql($SqlServer, "select * from T_C_User where Fname='{$Fname}' AND Fsid={$Fsid}");
        if(count($user)){
        	$user = $user[0];
            $temp = explode("|", $user['FDBConn']);
            if(count($temp) >= 4){
                $pdaUser = [
                    "ip" => $temp[0],
                    "dbName" => $temp[1],
                    "dbUserName" => $temp[2],
                    "dbUserPwd" => $temp[3],
                    "CustomerID" => 0,
                    "CreatorID" => $user['FName']
                ];
                $SqlServer = sqlsrv_connect($pdaUser['ip'],[ "UID" => $pdaUser['dbUserName'], "PWD" => $pdaUser['dbUserPwd'], "Database" => $pdaUser['dbName']]);
				$userC = SqlServerUtil::execSql($SqlServer, "select pda.FName,pda.FViewPrice,pda.FSID,cu.CustomerID,cu.ChiName FROM T_PDAUser as pda LEFT JOIN Customer as cu ON pda.FNumber = cu.CustomerID WHERE pda.FName = '{$Fname}' AND pda.FSID = '{$Fsid}'");
				if(count($userC)){
					$pdaUser['CustomerID'] = $userC[0]['CustomerID'];
				}
                return $pdaUser;
            }else{
                return false;
            }
        }
        return false;
	}

	//计算数量
	static function checkCountStock($SqlServer, $table, $data, $oddNumber){
		$data = is_array($data) ? $data : json_decode($data, true);
		if($table == 'OutStockDetail'){
			$DetailCargoCode = SqlServerUtil::get($SqlServer, $table, ['OutStockNo' => $oddNumber], ['OutStockNo','CargoCode','Qty', 'MakeOrderNo']);
		}else{
			$DetailCargoCode = SqlServerUtil::get($SqlServer, $table, ['InStockNo' => $oddNumber], ['InStockNo','CargoCode','Qty', 'MakeOrderNo']);
		}
		$successArr = [];
		//第一次对比查询的数量是否一致
		if($DetailCargoCode){
			if(count($DetailCargoCode)){
				foreach ($DetailCargoCode as $key => $value) {
					foreach ($data as $index => $item) {
						if(isset($item['update']) ){
							array_push($successArr, $item);
						}else{
							if($value['CargoCode'] == $item['CargoCode'] && $item['Qty'] <= $value['Qty']){
								array_push($successArr, $item);
							}
						}
					}
				}
				if(count($successArr) == count($data)){
					return $DetailCargoCode;
				}else{
					return "提交的物料数量不能超过明细中物料的数量";
				}
			}else{
				return "提交的物料不存在该明细中";
			}
		}else{
			return "明细中没有该物料请检查";
		}
		return true;
	}

	//获取
	static function getModel($request){
		$sqlCon = ['Customer' => null, 'SqlServer' => null];
	
		$DB = $request->get("db") ? $request->get("db") : env('DB_DATABASE_NAME');
		$serverConfig = [ "UID" => env("DB_USERNAME"), "PWD" => env("DB_PASSWORD"), "Database" => $DB];
        $sqlCon['SqlServer'] = sqlsrv_connect(env("DB_HOST"), $serverConfig);
       
        if($sqlCon['SqlServer']){
            return $sqlCon;
        }
        return false;
	}
	
	static function getModelDb($request,$db){
		$sqlCon = ['Customer' => null, 'SqlServer' => null];
		// $enCode = $request->cookie('wms_auth');
		// $serverConfig = [];
		//       if($enCode){
		//           $db = SqlServerUtil::_getDBInfo($enCode);
		//           $sqlCon['Customer'] = $db;
		//           $serverConfig = [ "UID" => $db['dbUserName'], "PWD" => $db['dbUserPwd'], "Database" => $db['dbName']];
		//           $host = $db['ip'];
		//       }else{
		//           if($request->get('Fname') != null){
		//               $db = SqlServerUtil::_getUserInfo($request->get('Fname'),$request->get('Fsid'));
		//               $sqlCon['Customer'] = $db;
		//               $DB = $request->get("db") ? $request->get("db") : $db['dbName'];
		//               $serverConfig = [ "UID" => $db['dbUserName'], "PWD" => $db['dbUserPwd'], "Database" => $DB];
		//               $host = $db['ip'];
		//           }else{
		//               return false;
		//           }
		//       }
		if($db ==1){
			$db = env("DB_DATABASE");
		}elseif($db ==2){
			$db = env("DB_DATABASE1");
			}else {
				$db = env("DB_DATABASE2");
			}
		
			$serverConfig = [ "UID" => env("DB_USERNAME"), "PWD" => env("DB_PASSWORD"), "Database" => $db];
		$sqlCon['SqlServer'] = sqlsrv_connect(env("DB_HOST"), $serverConfig);
		
		if($sqlCon['SqlServer']){
			return $sqlCon;
		}
		return false; 
	}
	
	static function getModelDbs($request){
		$sqlCon = ['Customer' => null, 'SqlServer' => null];
		$DB = $request->get("db") ? $request->get("db") : env('DB_DATABASE');
		
		$serverConfig = [ "UID" => env("DB_USERNAME"), "PWD" => env("DB_PASSWORD"), "Database" =>  $DB];
		$sqlCon['SqlServer'] = sqlsrv_connect(env("DB_HOST"), $serverConfig);
		if($sqlCon['SqlServer']){
			return $sqlCon;
		}
		return false;
	}
	
	/**
	 * 获取表单数据
	 * @date: 2019年3月16日 上午10:06:19
	 * @author: yy
	 * @param: variable
	 * @return:
	 */
	static function getList($server, $table = null, $where = [], $select = [], $limit = false){
	    if(!$server){
	        return false;
	    }
	    $select = SqlServerUtil::_createSelect($select);
	    $where = SqlServerUtil::_arrayToWhere($where);
	    if($limit){
	        //ORDER BY ActionTime DESC
	        $query = sqlsrv_query($server, "select TOP ".$limit." ". $select ." from ".$table.$where);
	    }else{
	        $query = sqlsrv_query($server, "select ". $select ." from ".$table.$where);
	    }
	    if(!$query) return false;
	    $result = [];
	    while($row = sqlsrv_fetch_array($query,SQLSRV_FETCH_ASSOC)){
	        foreach ($row as $key => $value) {
	            //sql server 乱码处理
	            if(is_string($row[$key])){
	                $row[$key] = iconv('GBK', 'UTF-8', $row[$key]);
	            }
	        }
	        
	        array_push($result, $row);
	    }
	    
	    return $result;
	}
	/**
	 * exc 执行语句
	 * @date: 2019年3月18日 下午3:41:17
	 * @author: yy
	 * @param: variable
	 * @return:
	 */
	static function exec($server, $sqlStr,$type =1){
	    if($server == false){
	        return false;
	    }
	    $resSql = sqlsrv_query($server, $sqlStr);
	    if(!$resSql) return false;
	    $result =[];
	    while($row = sqlsrv_fetch_array($resSql,SQLSRV_FETCH_ASSOC)){
	        foreach ($row as $key => $value) {
	        	//Var_dump(emptyStr($row[$key]),is_string($row[$key]),123 );
	             if(is_string($row[$key])){
                    //$row[$key] = iconv('GBK', 'UTF-8', $row[$key]);
	             	$row[$key] = mb_convert_encoding(emptyStr($row[$key]), "UTF-8", "GBK");
	             }elseif(is_int($row[$key])){
	             	$row[$key] = intval($row[$key]);
	             }elseif (is_float($row[$key])){
	             	$row[$key] = floatVal($row[$key]);
	             }elseif(is_object($row[$key])){
	             	//var_dump(date('Y-m-d H:i:s',$row[$key]->getTimestamp() ) );die;
	             	// isset($res[$key+1]['FActionTime'])?($res[$key+1]['FActionTime']->getTimestamp()):0;
	             	$row[$key] = isset($row[$key])?date('Y-m-d H:i:s',$row[$key]->getTimestamp()):''  ;
	             }else{
	             	$row[$key] = '';
	             }
	        }
	        array_push($result, $row);
	    }
	    sqlsrv_free_stmt( $resSql);
	    if($result && $type ==2)return $result[0];
	    return $result;
	}
	
	static function exec1($server, $sqlStr,$type =1){
		if($server == false){
			return false;
		}
		$sqlStr = iconv('UTF-8', 'GBK', $sqlStr);
		$resSql = sqlsrv_query($server, $sqlStr);
		if(!$resSql) return false;
		$result =[];
		while($row = sqlsrv_fetch_array($resSql,SQLSRV_FETCH_ASSOC)){
			foreach ($row as $key => $value) {
				//Var_dump(emptyStr($row[$key]),is_string($row[$key]),123 );
				if(is_string($row[$key])){
					//$row[$key] = iconv('GBK', 'UTF-8', $row[$key]);
					$row[$key] = mb_convert_encoding(emptyStr($row[$key]), "UTF-8", "GBK");
				}elseif(is_int($row[$key])){
					$row[$key] = intval($row[$key]);
				}elseif (is_float($row[$key])){
					$row[$key] = floatVal($row[$key]);
				}elseif(is_object($row[$key])){
					//var_dump(date('Y-m-d H:i:s',$row[$key]->getTimestamp() ) );die;
					// isset($res[$key+1]['FActionTime'])?($res[$key+1]['FActionTime']->getTimestamp()):0;
					$row[$key] = isset($row[$key])?date('Y-m-d H:i:s',$row[$key]->getTimestamp()):''  ;
				}else{
					$row[$key] = '';
				}
			}
			array_push($result, $row);
		}
		sqlsrv_free_stmt( $resSql);
		if($result && $type ==2)return $result[0];
		return $result;
	}
	
	/**
	 * 修改
	 * @date: 2019年3月19日 下午4:29:50
	 * @author: yy
	 * @param: variable
	 * @return:
	 */
	static function ydUpdate($server,$tbName, array $data,$where) {
	    if (!$data) return;
	    $valArr = [];
	    $valStr='';
	    foreach ($data as $k => $v) {
	        $valArr[] = $k . "='" . $v ."'";
	    }
	    $valStr = implode(',', $valArr);
	    $sql    = "update " . trim($tbName) . " set " . trim($valStr) . " where id = " . trim($where);
	    $return = sqlsrv_query($server,$sql);
	    if(empty($return)) return false;
	    return true;
	}
	
	
	/**
	* 循环插入 返回 id值
	* @date: 2019年4月18日 上午10:25:07
	* @author: yy
	*/
	static function insertALL($server, $table = null, $data = null){
	    $count = count($data);
	    $temp = [];
	    foreach ($data as $index => $item) {
	        $data = SqlServerUtil::_arrayToSql($item);
	        
	        $str = "insert into {$table}({$data['table_name']}) values({$data['table_value']})";
	        $update = sqlsrv_query($server, $str);
	        if($update){
	            $res = SqlServerUtil::execSql($server,"select IDENT_CURRENT('{$table}')");
	            array_push($temp, ['id' => $res[0][0]]);
	        }else{
	            dd(iconv('GBK', 'UTF-8', sqlsrv_errors()[0]['message'])); 
	            
	        }
	    }
	    return $temp;
	    return count($temp) ==$count ? true : false;
	}
	
	static function ksUpdate($server,$tbName, array $data,$where) {
		if (!$data) return;
		$valArr = [];
		$valStr='';
		foreach ($data as $k => $v) {
			$valArr[] = $k . "='" . $v ."'";
		}
		$valStr = implode(',', $valArr);
		$sql    = "update " . trim($tbName) . " set " . trim($valStr) . " where fid = " . trim($where);
		$return = sqlsrv_query($server,$sql);
		//var_dump($return);
		if(empty($return)) return false;
		return true;
	}
	
	//增加数据
	static function insert($server, $table = null, $data = null){
		$data = SqlServerUtil::_arrayToSql($data);
		$insert = sqlsrv_query($server, "insert into {$table}({$data['table_name']}) values({$data['table_value']})");
		$t = sqlsrv_errors();
		
		if($server == false || $t){
			dd(iconv('GBK', 'UTF-8', $t[0]['message']));
		}
		if($insert){
			return true;
		}
		return false;
	}
	
	static function sqlexecNo($server, $sqlStr){
		if($server == false){
			return false;
		}
		$sqlStr = iconv('UTF-8', 'GBK', $sqlStr);
		$result = sqlsrv_query($server, $sqlStr);
		if(!$result) return false;
		return true;
	}
	
	static function sqlexec($server, $sqlStr){
		if($server == false){
			return false;
		}
		$resSql = sqlsrv_query($server, $sqlStr);
		$result = sqlsrv_rows_affected($resSql);
		if(!$result) return false;
		return true;
	}
	
	static function sqlexecTo($server, $sqlStr){
		if($server == false){
			return false;
		}
		$resSql = sqlsrv_query($server, $sqlStr);
		$result = sqlsrv_rows_affected($resSql);
		if($resSql && $result) return true;
		if($resSql && $result == -1) {
			return true;
		}
		return true;
	}
	
	//
	static function sqlexecToTO($server, $sqlStr){
		if($server == false){
			return false;
		}
		$sqlStr = iconv( 'UTF-8','GBK',$sqlStr);
		$resSql = sqlsrv_query($server, $sqlStr);
		$result = sqlsrv_rows_affected($resSql);
		if($resSql && $result) return true;
		if($resSql && $result == -1) {
			return true;
		}
		return true;
	}
	
	static function sqlexecTosql($server, $sqlStr){
		if($server == false){
			return false;
		}
		$sqlStr = iconv( 'UTF-8','GBK',$sqlStr);
		$resSql = sqlsrv_query($server, $sqlStr);
		$result = sqlsrv_rows_affected($resSql);
		if(!$result) return false;
		return true;
	}
	
	
	static function kshiupdate($server, $table = null, $data = [], $where = []){
		$tableName = '';
		$tableValue = '';
		$setStr = "";
		$whereStr = "";
		foreach ($data as $key => $value) {
			$setStr .= $key."='".$value."',";
		}
		$setStr = substr($setStr,0,strlen($setStr)-1);
		if(count($where)){
			foreach ($where as $key => $value) {
				$whereStr .= $key."='".$value."' and ";
			}
			
			$whereStr = " WHERE ".substr($whereStr,0,strlen($whereStr)-5);
		}
		$update = sqlsrv_query($server, "update {$table} set {$setStr} {$whereStr}");
		return $update;
	}
	
	static function insertExec($server, $table = null, $data = null){
			$data = SqlServerUtil::_arrayToSql($data);
			$sql = "insert into {$table}({$data['table_name']}) values({$data['table_value']})" ;
			$sql = iconv( 'UTF-8','GBK',$sql);
			
			$insert = sqlsrv_query($server, $sql);
			$t = sqlsrv_errors();
			if($server == false || $t){
				dd(iconv('GBK', 'UTF-8', $t[0]['message']));
			}
			if($insert){
				$res = SqlServerUtil::execSql($server,"select IDENT_CURRENT('{$table}')");
				return ['id' => $res[0][0]];
			}
			return ['id' =>0];
		}
		
		/**
		 * 删除
		 * @date: 2020年3月13日 下午4:17:53
		 * @author: yy
		 */
		static function sqlsrvDelete($server, $table = null, $where = [],$whereFalse = false){
			if(count($where)<=0) return false; 
			if(empty($table)) return false; 
			$whereStr ='';
			if($whereFalse){
				$whereStr  = self::_arrayToWhereFids($where);
			}else{
				$whereStr  = self::_arrayToWhere($where);
			}
			if($server == false){
				return false;
			}
			$sqlStr = "delete from {$table} $whereStr";
			$resSql = sqlsrv_query($server, $sqlStr);
			$result = sqlsrv_rows_affected($resSql);
			
			sqlsrv_free_stmt( $resSql);
			if(!$result) return false;
			return true;
		}
		
		static function sqlsrvDeleteBack($server, $table = null, $where = [],$whereFalse = false){
			if(count($where)<=0) return false;
			if(empty($table)) return false;
			$whereStr ='';
			if($whereFalse){
				$whereStr  = self::_arrayToWhereFids($where);
			}else{
				$whereStr  = self::_arrayToWhere($where);
			}
			if($server == false){
				return false;
			}
			$sqlStr = "delete from {$table} $whereStr";
// 			echo $sqlStr;die;
			$resSql = sqlsrv_query($server, $sqlStr);
// 			dd($resSql);
			if(!$resSql) return false;
			return true;
		}
		
		
		static function execToSql($server, $sqlStr,$type =1){
			if($server == false){
				return false;
			}
			$sqlStr = iconv('UTF-8', 'GBK', $sqlStr);
			$resSql = sqlsrv_query($server, $sqlStr);
			if(!$resSql) return false;
			$result =[];
			while($row = sqlsrv_fetch_array($resSql,SQLSRV_FETCH_ASSOC)){
				foreach ($row as $key => $value) {
					//sql server 乱码处理
					if(is_string($row[$key])){
						$row[$key] = iconv('GBK', 'UTF-8', $row[$key]);
					}
				}
				array_push($result, $row);
			}
			sqlsrv_free_stmt( $resSql);
			if($result && $type ==2)return $result[0];
			return $result;
		}
		
		//sqlsrvCreateIdTosql
		static function sqlsrvCreateTosql($server, $table = null, $data = [], $where = []){
			$data = SqlServerUtil::_arrayEmptyToSqlNumber($data);
			$sql="insert into {$table}({$data['table_name']}) values({$data['table_value']})";
// 			$sql= iconv('UTF-8', 'GBK', $sql);
			
			$sql = mb_convert_encoding( $sql ,'GBK' , 'UTF-8' ) ; 
			$insert = sqlsrv_query($server, $sql);
			$t = sqlsrv_errors();
			if($server == false || $t){
				showMsg(400,iconv('GBK', 'UTF-8', $t[0]['message']));
			}
			if($insert){
				return true;
			}
			return false;
		}
		
		static function sqlsrvCreate($server, $table = null, $data = [], $where = []){
 
			$data = SqlServerUtil::_arrayEmptyToSql($data);
			
			$sql="insert into {$table}({$data['table_name']}) values({$data['table_value']})";
// 			echo $sql;die;
			$sql=iconv('UTF-8', 'GBK', $sql);
			$insert = sqlsrv_query($server, $sql);
			$t = sqlsrv_errors();
			if($server == false || $t){
				showMsg(400,iconv('GBK', 'UTF-8', $t[0]['message'])) ;
			}
			if($insert){
				return true;
			}
			return false;
		}
		
		/**
		* 函数用途描述
		* @date: 2023年2月15日 下午4:40:18
		* @author: juyi
		* @param: variable
		* @return:
		*/
		static function sqlsrvCreateID($server, $table = null, $data = [], $where = []){
			
			$data = SqlServerUtil::_arrayEmptyToSql($data);
			
			$sql="insert into {$table}({$data['table_name']}) values({$data['table_value']})";
			
			$sql=iconv('UTF-8', 'GBK', $sql);
			$insert = sqlsrv_query($server, $sql);
			$t = sqlsrv_errors();
			if($server == false || $t){
				showMsg(400,iconv('GBK', 'UTF-8', $t[0]['message'])) ;
			}
			if($insert){
				$res = SqlServerUtil::execTosqlOK($server,"select IDENT_CURRENT('{$table}') as id" ,2 );
				return $res['id'];
			}
			return false;
		}
		
		
		
		
		/**
		 * 聚一科技 ^_^
		 * 转sql
		 * @date: 2020年7月17日
		 * @author: lq
		 * @version:App V2.0
		 */
		static function sqlsrvAllTosql($server, $table = null, $data = []){
			$data = SqlServerUtil::arrayData($data);
			
			$sql = "insert into {$table}({$data['table_key']}) values {$data['table_value']}";
// 			echo $sql;die;
// 			$sql = iconv('UTF-8', 'GBK', $sql);
			$sql = mb_convert_encoding( $sql ,'GBK' , 'UTF-8' ) ; 
// 			dd( mb_convert_encoding( $sql ,'GBK' , 'UTF-8' ) );die; 
			
			$insert = sqlsrv_query($server, $sql);
			$t = sqlsrv_errors();
			if($server == false || $t){
				showMsg(400,iconv('GBK', 'UTF-8', $t[0]['message']));
			}
			if($insert){
				return true;
			}
			return false;
		}
		
		static function sqlsrvAll($server, $table = null, $data = []){
			$data = SqlServerUtil::arrayData($data);
			$sql="insert into {$table}({$data['table_key']}) values {$data['table_value']}";
			//$sql=iconv('UTF-8', 'GBK', $sql);
			$insert = sqlsrv_query($server, $sql);
			$t = sqlsrv_errors();
			if($server == false || $t){
				dd(iconv('GBK', 'UTF-8', $t[0]['message']));
			}
			if($insert){
				return true;
			}
			return false;
		}
		static function exeupdateTosql($server, $table = null, $data = [], $where = []){
			$tableName = ''; 
			$tableValue = ''; 
			$setStr = "";
			$whereStr = "";
			foreach ($data as $key => $value) {
				$setStr .= $key."='".$value."',";
			}
			$setStr = substr($setStr,0,strlen($setStr)-1);
			if(count($where)){
				foreach ($where as $key => $value) {
					$whereStr .=' '.$key."='".$value."' and";
				}
				$whereStr = " WHERE ".substr($whereStr,0,strlen($whereStr)-3);
			}
			$sql= "update {$table} set {$setStr} {$whereStr}";
// 			echo $sql;die;
			$sql=iconv('UTF-8', 'GBK', $sql);
			$update = sqlsrv_query($server, $sql);
			return $update;
		}
		static function sqlsrvkqUpdate($server, $tableName = null, $multipleData = [],$fid='fid'){
			$firstRow = current($multipleData);
			$updateColumn = array_keys($firstRow);
			$referenceColumn = isset($firstRow['id']) ? 'id' : $fid;
			foreach( $updateColumn as $k=>$v) {
				if($fid == $v) unset($updateColumn[$k]);
			}
			// 拼接sql语句
			$updateSql = "UPDATE " . $tableName . " SET ";
			$sets  = [];
			$bindings = [];
			
			foreach ($updateColumn as $uColumn) {
				$setSql = "" . $uColumn . " = CASE ";
				foreach ($multipleData as $data) {
					$setSql .= "WHEN " . $referenceColumn . " = '".$data[$referenceColumn]."' THEN " .$data[$uColumn] ;
					// 					$bindings[] = $data[$referenceColumn];
					// 					$bindings[] = $data[$uColumn];
				}
				$setSql .= " ELSE " . $uColumn . " END ";
				$sets[] = $setSql;
			}
			$updateSql .= implode(', ', $sets);
// 			echo $updateSql;die;
			$update = sqlsrv_query($server, $updateSql);
			return $update;
		}
		
		static function sqlsrvkqUpdateTosql($server, $tableName = null, $multipleData = [],$fid='fid'){
			$firstRow = current($multipleData);
			$updateColumn = array_keys($firstRow);
			$referenceColumn = isset($firstRow['id']) ? 'id' : $fid;
			foreach( $updateColumn as $k=>$v) {
				if($fid == $v) unset($updateColumn[$k]);
			}
			// 拼接sql语句
			$updateSql = "UPDATE " . $tableName . " SET ";
			$sets  = [];
			$bindings = [];
			
			foreach ($updateColumn as $uColumn) {
				$setSql = "" . $uColumn . " = CASE ";
				foreach ($multipleData as $data) {
					$setSql .= "WHEN " . $referenceColumn . " = '".$data[$referenceColumn]."' THEN '".$data[$uColumn]."'" ;
					// 					$bindings[] = $data[$referenceColumn];
					// 					$bindings[] = $data[$uColumn];
				}
				$setSql .= " ELSE " . $uColumn . " END ";
				$sets[] = $setSql;
			}
			$updateSql .= implode(', ', $sets);
// 			echo $updateSql;die;
			$updateSql = iconv('UTF-8', 'GBK', $updateSql);
			$update = sqlsrv_query($server, $updateSql);
			return $update;
		}
		
		static function sqlsrvUpdate($server, $tableName = null, $multipleData = [],$fid='fid'){
			$firstRow = current($multipleData);
			$updateColumn = array_keys($firstRow);
			
			$referenceColumn = isset($firstRow['id']) ? 'id' : $fid;
			foreach( $updateColumn as $k=>$v) {
				if($fid == $v) unset($updateColumn[$k]);
			}
			// 拼接sql语句
			$updateSql = "UPDATE " . $tableName . " SET ";
			$sets  = [];
			$bindings = [];
			
			foreach ($updateColumn as $uColumn) {
				$setSql = "" . $uColumn . " = CASE ";
				foreach ($multipleData as $data) {
					$setSql .= "WHEN " . $referenceColumn . " = '".$data[$referenceColumn]."' THEN " .$uColumn. '+'.$data[$uColumn] ;
					// 					$bindings[] = $data[$referenceColumn];
					// 					$bindings[] = $data[$uColumn];
				}
				$setSql .= " ELSE " . $uColumn . " END ";
				$sets[] = $setSql;
			}
			$updateSql .= implode(', ', $sets);
			$update = sqlsrv_query($server, $updateSql);
			return $update;
		}
		
		static function sqlsrvkqUpdatejia($server, $tableName = null, $multipleData = [],$jia='+'){
			$firstRow = current($multipleData);
			$updateColumn = array_keys($firstRow);
			$referenceColumn = isset($firstRow['id']) ? 'id' : 'fid';
			foreach( $updateColumn as $k=>$v) {
				if('fid' == $v) unset($updateColumn[$k]);
			}
			// 拼接sql语句
			$updateSql = "UPDATE " . $tableName . " SET ";
			$sets  = [];
			$bindings = [];
			
			foreach ($updateColumn as $uColumn) {
				$setSql = "" . $uColumn . " = CASE ";
				foreach ($multipleData as $data) {
					$setSql .= "WHEN " . $referenceColumn . " = '".$data[$referenceColumn]."' THEN " .$uColumn. $jia .$data[$uColumn] ;
					//$bindings[] = $data[$referenceColumn];
					//$bindings[] = $data[$uColumn];
				}
				$setSql .= " ELSE " . $uColumn . " END ";
				$sets[] = $setSql;
			}
			$updateSql .= implode(', ', $sets);
			$update = sqlsrv_query($server, $updateSql);
			return $update;
		}
		
		static function _arrayEmptyToSql($data){
			$result = ['table_name' => '', 'table_value' => ''];
			$name = [];
			$value = [];
			foreach ($data as $index => $item) {
				$item = str_replace("\r","",$item);
				$item = str_replace("\n","",$item);
				if(!is_array($item)){
					array_push($name, $index);
					array_push($value, "'".$item."'");
				}
				
			}
			$result['table_name'] = implode(",", $name);
			$result['table_value'] = implode(",", $value);
			return $result;
		}
		
		static function _arrayEmptyToSqlNumber($data){
			$result = ['table_name' => '', 'table_value' => ''];
			$name = [];
			$value = [];
			foreach ($data as $index => $item) {
				$item = str_replace("\r","",$item);
				$item = str_replace("\n","",$item);
				if(!is_array($item)){
					array_push($name, $index);
					if(is_numeric($item)){
						array_push($value, $item);
					}else{
						array_push($value, "'".$item."'");
					}
				}
			}
			$result['table_name'] = implode(",", $name);
			$result['table_value'] = implode(",", $value);
			return $result;
		}
		static function arrayData($data){
			$result = ['table_key' => '', 'table_value' => ''];
			$name = [];
			$value = [];
			$key='';
			$values='';
			$ff=[];
			foreach ($data as $index => $items) {
				$value =[];
				foreach ($items as $k =>$v)
				{
					array_push($name, $k);
					array_push($value, "'".$v."'");
				}
				$ff[$index] = '('.implode(",", $value) .')';
			}
			$values = implode(",", $ff);
			$key = join(',',array_keys($data[$index]));
			$itemStr = rtrim($values, ',');
			$result['table_key'] = $key;
			$result['table_value'] = $itemStr;
			return $result;
		}
		
		static function sqlsrvkqUpdateNO($server, $tableName = null, $multipleData = []){
			
			$firstRow = current($multipleData);
			$updateColumn = array_keys($firstRow);
			// 默认以id为条件更新，如果没有ID则以第一个字段为条件current($updateColumn)
			$referenceColumn = isset($firstRow['id']) ? 'id' : 'fitemid';
			//unset($updateColumn[0]);
			foreach( $updateColumn as $k=>$v) {
				if('fitemid' == $v) unset($updateColumn[$k]);
			}
			// 拼接sql语句
			$updateSql = "UPDATE " . $tableName . " SET ";
			$sets  = [];
			$bindings = [];
			foreach ($updateColumn as $uColumn) {
				$setSql = "" . $uColumn . " = CASE ";
				foreach ($multipleData as $data) {
					$setSql .= "WHEN " . $referenceColumn . " = '".$data[$referenceColumn]."' THEN '".$data[$uColumn]."'";
					// 					$bindings[] = $data[$referenceColumn];
					// 					$bindings[] = $data[$uColumn];
				}
				$setSql .= "ELSE " . $uColumn . " END ";
				$sets[] = $setSql;
			}
			$updateSql .= implode(', ', $sets);
// 			echo $updateSql;die;
			$updateSql = iconv('UTF-8','GBK',  $updateSql);
			
			$update = sqlsrv_query($server, $updateSql);
			return $update;
		}
		
		static function sqlsrvkqUpdateNOFID($server, $tableName = null, $multipleData = [] ){
			$firstRow = current($multipleData);
			$updateColumn = array_keys($firstRow);
			// 默认以id为条件更新，如果没有ID则以第一个字段为条件current($updateColumn)
			$referenceColumn = isset($firstRow['fid']) ? 'fid' : 'fitemid';
			//unset($updateColumn[0]);
			foreach( $updateColumn as $k=>$v) {
				if($referenceColumn == $v) unset($updateColumn[$k]);
			}
			// 拼接sql语句
			$updateSql = "UPDATE " . $tableName . " SET ";
			$sets  = [];
			$bindings = [];
			foreach ($updateColumn as $uColumn) {
				$setSql = "" . $uColumn . " = CASE ";
				foreach ($multipleData as $data) {
					$setSql .= "WHEN " . $referenceColumn . " = '".$data[$referenceColumn]."' THEN '".$data[$uColumn]."'";
					// 					$bindings[] = $data[$referenceColumn];
					// 					$bindings[] = $data[$uColumn];
				}
				$setSql .= "ELSE " . $uColumn . " END ";
				$sets[] = $setSql;
			}
			$updateSql .= implode(', ', $sets);
// 			echo $updateSql;die;
			$updateSql = iconv('UTF-8','GBK',  $updateSql);
				
			$update = sqlsrv_query($server, $updateSql);
			return $update;
		}
		
		static function execTosqlOK5($server,$sqlStr, $type = 1)
		{
			
			$sqlStr =   iconv('UTF-8', 'GBK', $sqlStr);
			$resSql = sqlsrv_query($server, $sqlStr);
			if (!$resSql) return false;
			$result = [];
			while ($row = sqlsrv_fetch_array($resSql, SQLSRV_FETCH_ASSOC)) {
				foreach ($row as $key => $value) {
					$key = iconv( 'GBK','UTF-8', $key) ;
					if (is_string($row[$key])) {
						//$row[$key] = iconv('GBK', 'UTF-8', $row[$key]);
						$row[$key] = mb_convert_encoding(emptyStr($row[$key]), "UTF-8", "GBK");
					} elseif (is_int($row[$key])) {
						$row[$key] = intval($row[$key]);
					} elseif (is_float($row[$key])) {
						$row[$key] = floatVal($row[$key]);
					} elseif (is_object($row[$key])) {
						//var_dump( ((array) new \DateTime())['date']::format('Y-m-d H:i:s') );
						$row[$key] = isset($row[$key])?date('Y-m-d H:i:s',$row[$key]->getTimestamp()):''  ;
					} else {
						$row[$key] = '';
					}
				}
				array_push($result, $row);
			}
			sqlsrv_free_stmt( $resSql);
			if ($result && $type == 2) return $result[0];
			return $result;
		}
		
	 
		 
		
		//执行语句 sql
		static function execTosqlOK($server,$sqlStr, $type = 1)
		{
			$sqlStr =   iconv('UTF-8', 'GBK', $sqlStr);
			$resSql = sqlsrv_query($server, $sqlStr);
// 			dd($resSql,555);
			if (!$resSql) return false;
			$result = [];
			while ($row = sqlsrv_fetch_array($resSql, SQLSRV_FETCH_ASSOC)) {
				foreach ($row as $key => $value) {
					if (is_string($row[$key])) {
						//$row[$key] = iconv('GBK', 'UTF-8', $row[$key]);
						$row[$key] = mb_convert_encoding(emptyStr($row[$key]), "UTF-8", "GBK");
					} elseif (is_int($row[$key])) {
						$row[$key] = intval($row[$key]);
					} elseif (is_float($row[$key])) {
						$row[$key] = floatVal($row[$key]);
					} elseif (is_object($row[$key])) {
						//var_dump( ((array) new \DateTime())['date']::format('Y-m-d H:i:s') );
						$row[$key] = isset($row[$key])?date('Y-m-d H:i:s',$row[$key]->getTimestamp()):''  ;
					} else {
						$row[$key] = '';
					}
				}
				array_push($result, $row);
			}
			sqlsrv_free_stmt( $resSql);
			if ($result && $type == 2) return $result[0];
			return $result;
		}
		
		
		
		static function execTosqlYes($server,$sqlStr, $type = 1)
		{
			$result = [];
			if (!$sqlStr) return $result;
			$resSql = sqlsrv_query($server, $sqlStr);
			
			while ($row = sqlsrv_fetch_array($resSql, SQLSRV_FETCH_ASSOC)) {
				foreach ($row as $key => $value) {
					if (is_string($row[$key])) {
						//$row[$key] = iconv('GBK', 'UTF-8', $row[$key]);
						$row[$key] = mb_convert_encoding(emptyStr($row[$key]), "UTF-8", "GBK");
					} elseif (is_int($row[$key])) {
						$row[$key] = intval($row[$key]);
					} elseif (is_float($row[$key])) {
						$row[$key] = floatVal($row[$key]);
					} elseif (is_object($row[$key])) {
						//var_dump( ((array) new \DateTime())['date']::format('Y-m-d H:i:s') );
						$row[$key] = isset($row[$key])?date('Y-m-d H:i:s',$row[$key]->getTimestamp()):''  ;
					} else {
						$row[$key] = '';
					}
				}
				array_push($result, $row);
			}
			sqlsrv_free_stmt( $resSql);
			if ($result && $type == 2) return $result[0];
			return $result;
		}
		
		/**
		* 字段名 中文问题
		* @date: 2023年3月5日 上午1:00:11
		* @author: juyi
		* @param: variable
		* @return:
		*/
		static function execTosqlOKTO($server,$sqlStr, $type = 1)
		{
			$sqlStr =   iconv('UTF-8', 'GBK', $sqlStr);
			$resSql = sqlsrv_query($server, $sqlStr);
			if (!$resSql) return false;
			$result = [];
			while ($row = sqlsrv_fetch_array($resSql, SQLSRV_FETCH_ASSOC)) {
				$rows=[];
				foreach ($row as $key => $value) {
					if (is_string($row[$key])) {
						//$row[$key] = iconv('GBK', 'UTF-8', $row[$key]);
						$rows[iconv('GBK', 'UTF-8', $key)] = mb_convert_encoding(emptyStr($row[$key]), "UTF-8", "GBK");
					} elseif (is_int($row[$key])) {
						$rows[iconv('GBK', 'UTF-8', $key)] = intval($row[$key]);
					} elseif (is_float($row[$key])) {
						$rows[iconv('GBK', 'UTF-8', $key)] = floatVal($row[$key]);
					} elseif (is_object($row[$key])) {
						//var_dump( ((array) new \DateTime())['date']::format('Y-m-d H:i:s') );
						$rows[iconv('GBK', 'UTF-8', $key)] = isset($row[$key])?date('Y-m-d H:i:s',$row[$key]->getTimestamp()):''  ;
					} else {
						$rows[iconv('GBK', 'UTF-8', $key)] = '';
					}
				}
// 				dd($rows);
				array_push($result, $rows);
			}
			sqlsrv_free_stmt( $resSql);
			if ($result && $type == 2) return $result[0];
			return $result;
		}
		
		
		static function execTosqlOKBAOJIA($server,$sqlStr, $type = 1)
		{
// 			$sqlStr =   iconv('UTF-8', 'GBK', $sqlStr);
			$sqlStr =   mb_convert_encoding( $sqlStr,"GBK"  , "UTF-8" );
			$resSql = sqlsrv_query($server, $sqlStr);
// 						dd($resSql,555);
			if (!$resSql) return false;
			$result = [];
			while ($row = sqlsrv_fetch_array($resSql, SQLSRV_FETCH_ASSOC)) {
				foreach ($row as $key => $value) {
					if (is_string($row[$key])) {
// 						$row[$key] = iconv('GBK', 'UTF-8', $row[$key]);
						$row[$key] = mb_convert_encoding(emptyStr($row[$key]), "UTF-8", "GBK");
					} elseif (is_int($row[$key])) {
						$row[$key] = intval($row[$key]);
					} elseif (is_float($row[$key])) {
						$row[$key] = floatVal($row[$key]);
					} elseif (is_object($row[$key])) {
						//var_dump( ((array) new \DateTime())['date']::format('Y-m-d H:i:s') );
						$row[$key] = isset($row[$key])?date('Y-m-d H:i:s',$row[$key]->getTimestamp()):''  ;
					} else {
						$row[$key] = '';
					}
				}
				array_push($result, $row);
			}
			sqlsrv_free_stmt( $resSql);
			if ($result && $type == 2) return $result[0];
			return $result;
		}
		
		
		
		
		
	 
}


